package repositorios;

import interfaceRepositorio.IRepositorioEndereco;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import entidades.Endereco;
import excecoes.FalhaPersistenciaException;

public class RepositorioEndereco extends RepositorioAbstrato implements
		IRepositorioEndereco {

	private String cadastraStm = "INSERT INTO TBENDERECO (TBServidor_cpf, logradouro, "
			+ "numero, complemento, bairro, municipio, uf, cep, email, telefone, ramal, celular) "
			+ "VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	private String consultaStm = "SELECT * FROM TBENDERECO WHERE TBServidor_cpf = ?";

	private String atualizaStm = "UPDATE TBENDERECO SET logradouro = ?, "
			+ "numero = ?, complemento = ?, bairro = ?, municipio = ?, uf = ?, cep = ?, "
			+ "email = ?, telefone = ?, ramal = ?, celular = ? WHERE TBServidor_cpf = ?";
	
	private String maxStm = "SELECT MAX(id) AS ultimo FROM TBENDERECO";

	public RepositorioEndereco() {
		super();
	}

	/**
	 * Cadastrar endereco
	 * 
	 * @param endereco
	 * @throws FalhaPersistenciaException
	 * @throws SQLException
	 */
	public void cadastrar(Endereco endereco, String cpfServidor) throws FalhaPersistenciaException,
			SQLException {

		Connection conn = encontrarConexao();
		PreparedStatement stm = conn.prepareStatement(cadastraStm);

		stm.setString(1, cpfServidor);
		stm.setString(2, endereco.getLogradouro());
		stm.setInt(3, endereco.getNumero());
		stm.setString(4, endereco.getComplemento());
		stm.setString(5, endereco.getBairro());
		stm.setString(6, endereco.getMunicipio());
		stm.setString(7, endereco.getEstado());
		stm.setString(8, endereco.getCep());
		stm.setString(9, endereco.getEmail());
		stm.setString(10, endereco.getTelefone());
		stm.setString(11, endereco.getRamal());
		stm.setString(12, endereco.getCelular());

		stm.execute();
		stm.close();
		
	}

	/**
	 * Consultar endereco
	 * 
	 * @param cpfServidor
	 * @return
	 * @throws FalhaPersistenciaException
	 * @throws SQLException
	 */
	public Endereco consultar(String cpfServidor)
			throws FalhaPersistenciaException, SQLException {

		Endereco endereco = null;

		Connection conn = encontrarConexao();
		PreparedStatement stm = conn.prepareStatement(consultaStm);

		stm.setString(1, cpfServidor);
		ResultSet result = stm.executeQuery();

		if (result.next()) {

			endereco = new Endereco();
			
			endereco.setLogradouro(result.getString("logradouro"));
			endereco.setNumero(result.getInt("numero"));
			endereco.setComplemento(result.getString("complemento"));
			endereco.setBairro(result.getString("bairro"));
			endereco.setMunicipio(result.getString("municipio"));
			endereco.setEstado(result.getString("uf"));
			endereco.setCep(result.getString("cep"));
			endereco.setEmail(result.getString("email"));
			endereco.setTelefone(result.getString("telefone"));
			endereco.setRamal(result.getString("ramal"));
			endereco.setCelular(result.getString("celular"));
		}
		stm.close();
		if(result!=null){
			result.close();
		}
		
		return endereco;
	}

	/**
	 * Atualizar endereco
	 * 
	 * @param endereco
	 * @throws FalhaPersistenciaException
	 * @throws SQLException
	 */
	public void atualizar(Endereco endereco,String cpfServidor) throws FalhaPersistenciaException,
			SQLException {

		Connection conn = encontrarConexao();
		PreparedStatement stm = conn.prepareStatement(atualizaStm);

		stm.setString(1, endereco.getLogradouro());
		stm.setInt(2, endereco.getNumero());
		stm.setString(3, endereco.getComplemento());
		stm.setString(4, endereco.getBairro());
		stm.setString(5, endereco.getMunicipio());
		stm.setString(6, endereco.getEstado());
		stm.setString(7, endereco.getCep());
		stm.setString(8, endereco.getEmail());
		stm.setString(9, endereco.getTelefone());
		stm.setString(10, endereco.getRamal());
		stm.setString(11, endereco.getCelular());
		stm.setString(12, cpfServidor);

		stm.execute();
		stm.close();
		
	}
	
	public int getMaximoId() throws FalhaPersistenciaException, SQLException {
		int retorno = 0;

		Connection conn = encontrarConexao();
		PreparedStatement stm = conn.prepareStatement(maxStm);

		ResultSet result = stm.executeQuery();
		if (result.next()) {
			retorno = result.getInt("ultimo");
		}
		stm.close();
		if(result!=null){
			result.close();
		}
		
		return retorno;
	}
	
}
